package main.service.mail.db;

import main.service.mail.bean.EnterpriseMailBean;
import main.service.mail.util.EmailUtils;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class JDBCMail {
    public static void create() {
        Db.execute("create table mail(id INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "mailAccount varchar(28),"
                + "subject varchar(28),"
                + "sender varchar(28),"
                + "receiveAddress varchar(28),"
                + "sendDate varchar(28),"
                + "priority varchar(28))");
    }

    public static void insert(String subject, String from, String receiveAddress, String sentDate,
                              String priority) {
        StringBuilder sql = new StringBuilder();
        sql.append("('");
        sql.append(EmailUtils.ACCOUNT);
        sql.append("', '");
        sql.append(subject);
        sql.append("', '");
        sql.append(from);
        sql.append("', '");
        sql.append(receiveAddress);
        sql.append("', '");
        sql.append(sentDate);
        sql.append("', '");
        sql.append(priority);
        sql.append("')");

        Db.execute("insert into mail(mailAccount, subject, sender, receiveAddress, sendDate, priority) values " + sql.toString());
    }

    public static ArrayList<EnterpriseMailBean> find(int page) {
        ArrayList<EnterpriseMailBean> beans = new ArrayList<>();
        String limit = "limit " + page + "," + (page+9);
        try {
            PreparedStatement statement = Db.getConn().prepareStatement("select * from mail where mailAccount = ? " + limit);
            statement.setString(1, EmailUtils.ACCOUNT);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                beans.add(mapToRow(resultSet));
            }
            statement.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return beans;
    }

    public static void drop() {
        try {
            Statement state = Db.getConn().createStatement();
            state.executeUpdate("drop table if exists mail");
            state.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    public static ArrayList<EnterpriseMailBean> findAll(String account) {
        ArrayList<EnterpriseMailBean> beans = new ArrayList<>();
        try {
            PreparedStatement statement = Db.getConn().prepareStatement("select * from mail");
//            statement.setString(1, account);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                beans.add(mapToRow(resultSet));
            }
            statement.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return beans;
    }

    public static EnterpriseMailBean mapToRow(ResultSet resultSet) throws Exception {
        EnterpriseMailBean bean = new EnterpriseMailBean();
        bean.setSubject(resultSet.getString("subject"));
        bean.setId(resultSet.getInt("id"));
        bean.setSender(resultSet.getString("sender"));
        bean.setReceiveAddress(resultSet.getString("receiveAddress"));
        bean.setSendDate(resultSet.getString("sendDate"));
        bean.setPriority(resultSet.getString("priority"));
        return bean;
    }
}
